package com.x2.base.db;

import cn.hutool.json.JSONUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.metadata.OrderItem;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.x2.core.util.ToolUtil;

import java.sql.*;
import java.util.*;

/**
 * @author 常鹏
 * @version 1.0
 * @date 2022/7/8 9:21
 */
public abstract class AbstractJdbc {

    /**
     * 数据源
     */
    private DruidDataSource dataSource;

    /**
     * 初始化
     */
    public abstract void init();

    /**
     * 创建数据源
     *
     * @param driver：驱动
     * @param url：连接地址
     * @param userName：用户名
     * @param password：密码
     */
    protected synchronized DruidDataSource createDataSource(String driver, String url, String userName, String password) throws Exception {
        try {
            if (dataSource == null) {
                dataSource = new DruidDataSource();
                dataSource.setUrl(url);
                dataSource.setDriverClassName(driver);
                dataSource.setUsername(userName);
                dataSource.setPassword(password);
                dataSource.setInitialSize(3);
                dataSource.setMinIdle(3);
                dataSource.setMaxActive(50);
                dataSource.setRemoveAbandoned(true);
                dataSource.setRemoveAbandonedTimeout(30);
                dataSource.setMaxWait(20000);
                dataSource.setTimeBetweenEvictionRunsMillis(20000);
                dataSource.setValidationQuery("SELECT 'x' ");
                dataSource.setTestWhileIdle(true);
                dataSource.setTestOnBorrow(true);
            }
            return dataSource;
        } catch (Exception ex) {
            throw new Exception("数据库连接失败");
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 数据库连接
     * @throws Exception
     */
    public Connection getConn() throws Exception {
        init();
        if (dataSource == null) {
            throw new Exception("获取数据库连接异常");
        }
        return dataSource.getConnection();
    }

    /**
     * 新增或修改语句执行
     *
     * @param sql：sql语句
     * @param param：参数
     * @return 插入结果 -1：失败
     * @throws Exception 抛出的编译异常
     */
    public long executeUpdate(String sql, Object[] param) throws Exception {
        PreparedStatement preparedStatement = null;
        Connection conn = null;
        try {
            if (sql == null) {
                return -1;
            }
            conn = getConn();
            boolean insert = checkInsert(sql);
            if (insert) {
                preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                preparedStatement = conn.prepareStatement(sql);
            }
            if (param != null && param.length > 0) {
                for (int i = 0; i < param.length; i++) {
                    if (param[i] != null && param[i] instanceof java.util.Date) {
                        preparedStatement.setObject(i + 1, new Timestamp(((java.util.Date) param[i]).getTime()));
                    } else {
                        preparedStatement.setObject(i + 1, param[i]);
                    }
                }
            }
            return preparedStatement.executeUpdate();
        } finally {
            close(conn, preparedStatement);
        }
    }

    /**
     * 批量更新
     *
     * @param sql：sql语句
     * @param paramList：参数
     * @return true：操作成功，false：操作失败
     * @throws Exception 异常
     */
    public boolean executeBatchUpdate(String sql, List<Object[]> paramList) throws Exception {
        PreparedStatement preparedStatement = null;
        boolean result = false;
        Connection conn = null;
        try {
            conn = getConn();
            preparedStatement = conn.prepareStatement(sql);
            if (paramList != null && paramList.size() > 0) {
                for (int i = 0; i < paramList.size(); i++) {
                    Object[] param = paramList.get(i);
                    if (param != null && param.length > 0) {
                        for (int j = 0; j < param.length; j++) {
                            preparedStatement.setObject(j + 1, param[j]);
                        }
                        preparedStatement.addBatch();
                    }
                }
            }
            int[] num = preparedStatement.executeBatch();
            if (num != null && num.length > 0) {
                result = true;
            }
        } finally {
            close(conn, preparedStatement);
        }
        return result;
    }

    /**
     * 数据查询
     *
     * @param sql：sql语句
     * @return 数据结果
     * @throws Exception 异常
     */
    public List<Map<String, Object>> executeQuery(String sql) throws Exception {
        return executeQuery(sql, true);
    }

    /**
     * 数据查询
     *
     * @param sql：sql语句
     * @param closeConn: 是否关闭连接
     * @return 数据结果
     * @throws Exception 异常
     */
    public List<Map<String, Object>> executeQuery(String sql, boolean closeConn) throws Exception {
        return executeQuery(sql, null, closeConn);
    }

    /**
     * 数据查询
     *
     * @param sql：sql语句
     * @param param:    参数
     * @return 数据结果
     * @throws Exception 异常
     */
    public List<Map<String, Object>> executeQuery(String sql, Object[] param) throws Exception {
        return executeQuery(sql, param, true);
    }

    /**
     * 数据查询
     *
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn: 是否关闭连接
     * @return 数据结果
     * @throws Exception 异常
     */
    public List<Map<String, Object>> executeQuery(String sql, Object[] param, boolean closeConn) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        Connection conn = null;
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            if (sql == null) {
                return result;
            }
            conn = getConn();
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                }
            }
            return result;
        } finally {
            if (closeConn) {
                close(conn, preparedStatement, res);
            } else {
                close(preparedStatement, res);
            }
        }
    }

    /**
     * 数据查询
     *
     * @param sql:         sql语句
     * @param resultClass: 结果数据类型
     * @param <T>：结果
     * @return 结果
     * @throws Exception 异常
     */
    public <T> List<T> queryEntity(String sql, Class<T> resultClass) throws Exception {
        return queryEntity(sql, true, resultClass);
    }

    /**
     * 数据查询
     *
     * @param sql:         sql语句
     * @param closeConn:   是否关闭连接
     * @param resultClass: 结果数据类型
     * @param <T>：结果
     * @return 结果
     * @throws Exception 异常
     */
    public <T> List<T> queryEntity(String sql, boolean closeConn, Class<T> resultClass) throws Exception {
        return queryEntity(sql, null, closeConn, resultClass);
    }

    /**
     * 数据查询
     *
     * @param sql:         sql语句
     * @param resultClass: 结果数据类型
     * @param <T>：结果
     * @return 结果
     * @throws Exception 异常
     */
    public <T> List<T> queryEntity(String sql, Object[] param, Class<T> resultClass) throws Exception {
        return queryEntity(sql, param, true, resultClass);
    }

    /**
     * 数据查询
     *
     * @param sql:         sql语句
     * @param param：参数
     * @param closeConn:   是否关闭连接
     * @param resultClass: 结果数据类型
     * @param <T>：结果
     * @return 结果
     * @throws Exception 异常
     */
    public <T> List<T> queryEntity(String sql, Object[] param, boolean closeConn, Class<T> resultClass) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<T> result = new ArrayList<>();
        Connection conn = null;
        try {
            if (sql == null) {
                return Collections.emptyList();
            }
            conn = getConn();
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(JSON.parseObject(JSONUtil.toJsonPrettyStr(resultRow), resultClass));
                }
            }
            return result;
        } finally {
            if (closeConn) {
                close(conn, preparedStatement, res);
            } else {
                close(preparedStatement, res);
            }
        }
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public Page<Map<String, Object>> executePage(Page page, String sql) throws Exception {
        return executePage(page, sql, true);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param closeConn：是否关闭连接
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public Page<Map<String, Object>> executePage(Page page, String sql, boolean closeConn) throws Exception {
        return executePage(page, sql, null, closeConn);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param：参数
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public Page<Map<String, Object>> executePage(Page page, String sql, Object[] param) throws Exception {
        return executePage(page, sql, param, true);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn：是否关闭连接
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public Page<Map<String, Object>> executePage(Page page, String sql, Object[] param, boolean closeConn) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<Map<String, Object>> result = new ArrayList<>();
        Connection conn = null;
        try {
            if (sql == null) {
                return new Page<>();
            }
            conn = getConn();
            long totalCount = executeCount(conn, sql, param, false);
            sql = appendOrder(sql, page.getOrders());
            sql = appendLimit(sql, page);
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                }
            }
            Page<Map<String, Object>> resultPage = new Page<>(page.getCurrent(), page.getSize(), totalCount);
            resultPage.setRecords(result);
            return resultPage;
        } finally {
            if (closeConn) {
                close(conn, preparedStatement, res);
            } else {
                close(preparedStatement, res);
            }
        }
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param resultClass:     结果数据bean
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public <T> Page<T> queryEntityPage(Page page, String sql, Class<T> resultClass) throws Exception {
        return queryEntityPage(page, sql, true, resultClass);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param closeConn:       是否关闭连接
     * @param resultClass:     结果数据bean
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public <T> Page<T> queryEntityPage(Page page, String sql, boolean closeConn, Class<T> resultClass) throws Exception {
        return queryEntityPage(page, sql, null, closeConn, resultClass);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param:           参数
     * @param resultClass:     结果数据bean
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public <T> Page<T> queryEntityPage(Page page, String sql, Object[] param, Class<T> resultClass) throws Exception {
        return queryEntityPage(page, sql, param, true, resultClass);
    }

    /**
     * 分页查询返回map
     *
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn:       是否关闭连接
     * @param resultClass:     结果数据bean
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public <T> Page<T> queryEntityPage(Page page, String sql, Object[] param, boolean closeConn, Class<T> resultClass) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<T> result = new ArrayList<>();
        Connection conn = null;
        try {
            if (sql == null) {
                return new Page<>();
            }
            conn = getConn();
            long totalCount = executeCount(conn, sql, param, false);
            sql = appendOrder(sql, page.getOrders());
            sql = appendLimit(sql, page);
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(JSON.parseObject(JSONUtil.toJsonPrettyStr(resultRow), resultClass));
                }
            }
            Page<T> resultPage = new Page<>(page.getCurrent(), page.getSize(), totalCount);
            resultPage.setRecords(result);
            return resultPage;
        } finally {
            if (closeConn) {
                close(conn, preparedStatement, res);
            } else {
                close(preparedStatement, res);
            }
        }
    }

    /**
     * 拼接排序信息
     *
     * @param sql：sql语句
     * @param orderItemList：排序信息
     * @return 拼装后的sql
     */
    private static String appendOrder(String sql, List<OrderItem> orderItemList) {
        if (orderItemList != null && orderItemList.size() > 0) {
            StringBuffer buffer = new StringBuffer();
            buffer.append(sql).append(" order by");
            int count = 0;
            for (OrderItem orderItem : orderItemList) {
                if (count == 0) {
                    buffer.append(" ");
                } else {
                    buffer.append(", ");
                }
                buffer.append(orderItem.getColumn() + " " + (orderItem.isAsc() ? "ASC" : "DESC"));
            }
            sql = buffer.toString();
        }
        return sql;
    }

    /**
     * 拼接分页
     *
     * @param sql:      sql语句
     * @param page：分页参数
     * @return 结果
     */
    private static String appendLimit(String sql, Page page) {
        return sql + " limit " + page.offset() + ", " + page.getSize();
    }

    /**
     * 查询数据数量
     *
     * @param conn:                                数据库连接
     * @param sql：sql语句
     * @param param：参数
     * @param closeConnection：是否关闭连接，false不关闭，自行关闭
     * @return 记录数量
     * @throws Exception 异常
     */
    private long executeCount(Connection conn, String sql, Object[] param, boolean closeConnection) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            if (sql == null) {
                return 0;
            }
            sql = "select count(*) AS TOTAL from (" + sql + ") t";
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                }
            }
            if (result.size() > 0) {
                return ToolUtil.toLong(result.get(0).get("TOTAL") + "", 0L);
            }
            return 0;
        } finally {
            closeRes(res);
            closePreparedStatement(preparedStatement);
            if (closeConnection) {
                releaseConnection(conn);
            }
        }
    }

    /**
     * 验证是否为插入语句
     *
     * @param sql: sql语句
     * @return true：是，false：否
     */
    private boolean checkInsert(String sql) {
        return (sql.indexOf("insert") > -1 || sql.indexOf("INSERT") > -1);
    }

    /**
     * 拼装查询条件
     *
     * @param conn：连接
     * @param sql：sql语句
     * @param param：条件
     * @return 语句块
     * @throws Exception 异常
     */
    private PreparedStatement getQueryStatement(Connection conn, String sql, Object[] param) throws Exception {
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        if (param != null && param.length > 0) {
            for (int i = 0; i < param.length; i++) {
                if (param[i] == null) {
                    break;
                }
                preparedStatement.setObject(i + 1, param[i]);
            }
        }
        return preparedStatement;
    }

    private void close(Connection conn, PreparedStatement preparedStatement) {
        closePreparedStatement(preparedStatement);
        releaseConnection(conn);
    }

    private void close(PreparedStatement preparedStatement, ResultSet res) {
        closeRes(res);
        closePreparedStatement(preparedStatement);
    }

    private void close(Connection conn, PreparedStatement preparedStatement, ResultSet res) {
        closeRes(res);
        closePreparedStatement(preparedStatement);
        releaseConnection(conn);
    }

    /**
     * 关闭
     *
     * @param preparedStatement: 对象
     */
    public void closePreparedStatement(PreparedStatement preparedStatement) {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 关闭结果集
     *
     * @param res: 结果集
     */
    public void closeRes(ResultSet res) {
        if (res != null) {
            try {
                res.close();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 关闭连接
     *
     * @param conn：数据库连接
     */
    public void releaseConnection(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

    /**
     * 以下是支持事务的方法
     * 调用tx_getConn()开启事务并返回数据库连接
     * 之后可调用下面的tx_开头的方法实现数据库操作
     * 最后调用tx_commit或tx_rollback方法提交或回滚事务
     */

    /**
     * 获取数据库连接并开启事务
     *
     * @return 数据库连接
     * @throws Exception 异常
     */
    public Connection tx_getConn() throws Exception {
        init();
        if (dataSource == null) {
            throw new Exception("获取数据库连接异常");
        }
        Connection conn = dataSource.getConnection();
        conn.setAutoCommit(false);
        return conn;
    }

    /**
     * 提交事务
     *
     * @param conn: 数据库连接
     */
    public void tx_commit(Connection conn) {
        try {
            conn.commit();
            conn.setAutoCommit(true);
            releaseConnection(conn);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 回滚事务
     *
     * @param conn: 数据库连接
     */
    public void tx_rollback(Connection conn) {
        try {
            conn.rollback();
            conn.setAutoCommit(true);
            releaseConnection(conn);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 新增或修改语句执行
     *
     * @param conn:     数据库连接
     * @param sql：sql语句
     * @param param：参数
     * @return 插入结果 -1：失败
     * @throws Exception 抛出的编译异常
     */
    public long tx_executeUpdate(Connection conn, String sql, Object[] param) throws Exception {
        PreparedStatement preparedStatement = null;
        try {
            if (sql == null) {
                return -1;
            }
            boolean insert = checkInsert(sql);
            if (insert) {
                preparedStatement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            } else {
                preparedStatement = conn.prepareStatement(sql);
            }
            if (param != null && param.length > 0) {
                for (int i = 0; i < param.length; i++) {
                    if (param[i] != null && param[i] instanceof java.util.Date) {
                        preparedStatement.setObject(i + 1, new Timestamp(((java.util.Date) param[i]).getTime()));
                    } else {
                        preparedStatement.setObject(i + 1, param[i]);
                    }
                }
            }
            return preparedStatement.executeUpdate();
        } finally {
            closePreparedStatement(preparedStatement);
        }
    }

    /**
     * 数据查询
     *
     * @param conn:      数据库连接
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn: 是否关闭连接
     * @return 数据结果
     * @throws Exception 异常
     */
    public List<Map<String, Object>> tx_executeQuery(Connection conn, String sql, Object[] param, boolean closeConn) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            if (sql == null) {
                return result;
            }
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                }
            }
            return result;
        } finally {
            close(preparedStatement, res);
        }
    }

    /**
     * 分页查询返回map
     *
     * @param conn:            数据库连接
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn：是否关闭连接
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public Page<Map<String, Object>> tx_executePage(Connection conn, Page page, String sql, Object[] param, boolean closeConn) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            if (sql == null) {
                return new Page<>();
            }
            long totalCount = executeCount(conn, sql, param, false);
            sql = appendOrder(sql, page.getOrders());
            sql = appendLimit(sql, page);
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(resultRow);
                }
            }
            Page<Map<String, Object>> resultPage = new Page<>(page.getCurrent(), page.getSize(), totalCount);
            resultPage.setRecords(result);
            return resultPage;
        } finally {
            close(preparedStatement, res);
        }
    }

    /**
     * 数据查询
     *
     * @param conn:        数据库连接
     * @param sql:         sql语句
     * @param param：参数
     * @param resultClass: 结果数据类型
     * @param <T>：结果
     * @return 结果
     * @throws Exception 异常
     */
    public <T> List<T> tx_queryEntity(Connection conn, String sql, Object[] param, Class<T> resultClass) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<T> result = new ArrayList<>();
        try {
            if (sql == null) {
                return Collections.emptyList();
            }
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(JSON.parseObject(JSONUtil.toJsonPrettyStr(resultRow), resultClass));
                }
            }
            return result;
        } finally {
            close(preparedStatement, res);
        }
    }

    /**
     * 分页查询返回map
     *
     * @param conn:            数据库连接
     * @param page：分页参数，包含排序参数
     * @param sql：sql语句
     * @param param：参数
     * @param closeConn:       是否关闭连接
     * @param resultClass:     结果数据bean
     * @return 分页信息及数据
     * @throws Exception 异常信息
     */
    public <T> Page<T> tx_queryEntityPage(Connection conn, Page page, String sql, Object[] param, boolean closeConn, Class<T> resultClass) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        List<T> result = new ArrayList<>();
        try {
            if (sql == null) {
                return new Page<>();
            }
            long totalCount = executeCount(conn, sql, param, false);
            sql = appendOrder(sql, page.getOrders());
            sql = appendLimit(sql, page);
            preparedStatement = getQueryStatement(conn, sql, param);
            res = preparedStatement.executeQuery();
            if (res != null) {
                int columnCount = res.getMetaData().getColumnCount();
                while (res.next()) {
                    Map<String, Object> resultRow = new HashMap<>(0);
                    for (int i = 1; i <= columnCount; i++) {
                        resultRow.put(res.getMetaData().getColumnLabel(i), res.getObject(i));
                    }
                    result.add(JSON.parseObject(JSONUtil.toJsonPrettyStr(resultRow), resultClass));
                }
            }
            Page<T> resultPage = new Page<>(page.getCurrent(), page.getSize(), totalCount);
            resultPage.setRecords(result);
            return resultPage;
        } finally {
            close(preparedStatement, res);
        }
    }
}
